{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 若无法导入，则pip install xlwings\n",
    "import xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "新建一个表格应用\n",
    "visible：是否可见\n",
    "add_book：是否新建工作簿\n",
    "\"\"\"\n",
    "app = xw.App(visible=True, add_book=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# 打开工作表\n",
    "wb = app.books.open('Data_samples/社会实践报名表.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开工作簿\n",
    "sht = wb.sheets[\"sheet1\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "<Range [社会实践报名表.xlsx]Sheet1!$A$1>\n目的地\n"
    }
   ],
   "source": [
    "# 读取特定单元格\n",
    "print(sht.range(\"A1\"))\n",
    "print(sht.range(\"A1\").value) #读取值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "目的地\n[['目的地', '描述'], ['阿富汗', '第2公司']]\n[['目的地', '描述'], ['阿富汗', '第2公司']]\n"
    }
   ],
   "source": [
    "print(sht[\"A1\"].value)\n",
    "print(sht[\"A1:B2\"].value)\n",
    "print(sht[\"$A$1:$B$2\"].value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "(19, 5)"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "# 非空单元格的范围\n",
    "sht.used_range.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "19"
     },
     "metadata": {},
     "execution_count": 8
    }
   ],
   "source": [
    "rows = sht.used_range.shape[0]#获取列数\n",
    "rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": "e2:e19\n"
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "['唱',\n '跳',\n 'rap',\n '跳',\n '跳',\n '唱',\n '唱',\n '跳',\n 'rap',\n '唱',\n '篮球',\n '篮球',\n 'rap',\n '唱',\n '跳',\n '唱',\n '跳',\n '唱']"
     },
     "metadata": {},
     "execution_count": 11
    }
   ],
   "source": [
    "#简单字符串处理，合成单元格范围\n",
    "col = \"e\"\n",
    "region = col+\"2:\"+col+ str(rows)\n",
    "print(region)\n",
    "\n",
    "#读取该范围的值\n",
    "sht[region].value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取 1~5 列的内容\n",
    "\n",
    "result = []\n",
    "for col in [\"A\", \"B\", \"C\", \"D\", \"E\"]:\n",
    "    region = col + \"2:\" + col + str(rows)\n",
    "    result.append(sht[region].value)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "[['阿富汗',\n  '伊拉克',\n  '约旦',\n  '黎巴嫩',\n  '以色列',\n  '巴勒斯坦',\n  '沙特阿拉伯',\n  '巴林',\n  '卡塔尔',\n  '科威特',\n  '阿拉伯联合酋长国',\n  '阿曼',\n  '也门',\n  '格鲁吉亚',\n  '亚美尼亚',\n  '阿塞拜疆',\n  '土耳其',\n  '塞浦路斯'],\n ['第2公司',\n  '第4公司',\n  '第4公司',\n  '第8公司',\n  '第2公司',\n  '第6公司',\n  '第8公司',\n  '第8公司',\n  '第8公司',\n  '第2公司',\n  '第3公司',\n  '第7公司',\n  '第7公司',\n  '第3公司',\n  '第6公司',\n  '第5公司',\n  '第2公司',\n  '第5公司'],\n ['电子',\n  '社科',\n  '电子',\n  '土木',\n  '工物',\n  '航院',\n  '航院',\n  '工物',\n  '工物',\n  '自动化',\n  '社科',\n  '社科',\n  '航院',\n  '电子',\n  '自动化',\n  '工物',\n  '航院',\n  '工物'],\n [11.0,\n  11.0,\n  9.0,\n  3.0,\n  15.0,\n  14.0,\n  4.0,\n  5.0,\n  18.0,\n  19.0,\n  5.0,\n  17.0,\n  12.0,\n  20.0,\n  8.0,\n  12.0,\n  12.0,\n  9.0],\n ['唱',\n  '跳',\n  'rap',\n  '跳',\n  '跳',\n  '唱',\n  '唱',\n  '跳',\n  'rap',\n  '唱',\n  '篮球',\n  '篮球',\n  'rap',\n  '唱',\n  '跳',\n  '唱',\n  '跳',\n  '唱']]"
     },
     "metadata": {},
     "execution_count": 13
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 关闭workbook 和 app"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用结束一定要记得关闭 workbook、退出 app\n",
    "wb.save() #保存工作表\n",
    "wb.close() #关闭工作簿\n",
    "app.quit() #退出表格应用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}